/**
 * 预约管理服务
 *
 * @fileoverview 提供预约记录的完整生命周期管理，包括 CRUD 操作、批量处理、状态管理等功能。
 * 这是预约系统的核心业务逻辑层，处理所有与预约记录相关的数据操作。
 *
 * 核心功能：
 * - 预约记录的 CRUD 操作
 * - 序号自动生成和去重
 * - 批量操作和状态管理
 * - 操作日志记录
 * - 统计信息计算
 * - 占号数据管理
 *
 * @author System
 * @version 1.0.0
 * @since 2024
 */

import type { PrismaClient } from '@prisma/client'
import type {
  AppointmentFilters,
  AppointmentListResult,
  AppointmentManagement,
  BatchOperationRequest,
  BatchOperationResult,
  CreateAppointmentInput,
  OperationType,
  PaginationParams,
  UpdateAppointmentInput,
} from '@shared/types/'
import { normalizeAppointmentTime } from '@shared/utils/appointment-time'
import { pickOrGenerateUA } from '@shared/utils/user-agent'
import dayjs from 'dayjs'
import { getDatabase } from '../db/index'
import { getLogger } from '../logger/index'
import 'dayjs/locale/zh-cn'

// 配置 dayjs 中文
dayjs.locale('zh-cn')

/**
 * 预约管理服务类
 *
 * @description 预约系统的核心服务层，提供预约记录的完整管理功能。
 * 使用 Prisma ORM 进行数据库操作，集成日志记录和错误处理。
 *
 * @class AppointmentService
 * @example
 * ```typescript
 * const service = getAppointmentService()
 * const appointment = await service.createAppointment({
 *   passport_number: 'P123456',
 *   license_number: 'L123456789',
 *   full_name: '张三',
 *   email: 'zhangsan@example.com'
 * })
 * ```
 */
export class AppointmentService {
  /** Prisma 数据库客户端 */
  private prisma: PrismaClient
  /** 日志记录器 */
  private logger = getLogger()
  /** 是否存在 is_monitor 列的缓存（减少重复 PRAGMA） */
  private hasIsMonitorColumnCache: boolean | null = null

  /**
   * 构造函数
   *
   * @description 初始化服务实例，获取数据库连接和日志记录器
   */
  constructor() {
    this.prisma = getDatabase()
  }

  /** 检测 appointment_management 表是否包含 is_monitor 列（带缓存） */
  private async hasIsMonitorColumn(): Promise<boolean> {
    if (this.hasIsMonitorColumnCache !== null)
      return this.hasIsMonitorColumnCache
    try {
      const rows: any[] = await (this.prisma as any).$queryRawUnsafe(`PRAGMA table_info('appointment_management');`)
      const ok = Array.isArray(rows) && rows.some((r: any) => String(r?.name || '') === 'is_monitor')
      this.hasIsMonitorColumnCache = !!ok
      return !!ok
    }
    catch {
      this.hasIsMonitorColumnCache = false
      return false
    }
  }

  /**
   * 将日期规范化为 YYYY-MM-DD（支持输入 YYYY-MM-DD 或 DD-MM-YYYY）
   */
  private normalizeDateToYMD(date?: string): string | undefined {
    if (!date)
      return undefined
    const s = String(date).trim()
    if (!s)
      return undefined
    if (/^\d{4}-\d{2}-\d{2}$/.test(s))
      return s
    const m = s.match(/^(\d{2})-(\d{2})-(\d{4})$/)
    if (m) {
      const [_, dd, mm, yyyy] = m
      return `${yyyy}-${mm}-${dd}`
    }
    return s
  }

  /**
   * 规范化首选日期数组为 YYYY-MM-DD
   */
  private normalizePreferredDates(dates?: string[]): string[] | undefined {
    if (!Array.isArray(dates) || dates.length === 0)
      return undefined
    const out: string[] = []
    for (const d of dates) {
      const ymd = this.normalizeDateToYMD(d)
      if (ymd)
        out.push(ymd)
    }
    return out.length > 0 ? out : undefined
  }

  /**
   * 生成唯一序号
   *
   * @description 为新的预约记录生成唯一序号，格式为：YYYYMMDD + 4位随机数。
   * 采用日期前缀保证时间有序性，随机后缀减少并发冲突。
   *
   * @returns {Promise<string>} 生成的序号字符串，如：202412251234
   *
   * @private
   * @example
   * ```typescript
   * const sequenceNumber = await this.generateSequenceNumber()
   * // 返回类似：20241225abcd
   * ```
   */
  private async generateSequenceNumber(): Promise<string> {
    const now = dayjs()
    const dateStr = now.format('YYYYMMDD')

    // 加盐随机段，减少并发碰撞，仍保持有序前缀
    const randomPart = Math.floor(Math.random() * 10000).toString().padStart(4, '0')
    return `${dateStr}${randomPart}`
  }

  /**
   * 记录操作日志
   *
   * @description 为预约记录的操作记录详细日志，包括操作类型、结果、操作数据等。
   * 用于审计跟踪和问题排查。
   *
   * @param {number} appointmentId - 预约记录ID
   * @param {OperationType} operationType - 操作类型（create/update/book/cancel/release）
   * @param {string} resultStatus - 操作结果状态（success/failed）
   * @param {string} [resultMessage] - 操作结果消息
   * @param {any} [operationData] - 操作相关数据
   * @param {string} [operator] - 操作者标识，默认为 'system'
   *
   * @returns {Promise<void>}
   *
   * @private
   */
  private async logOperation(
    appointmentId: number,
    operationType: OperationType,
    resultStatus: string,
    resultMessage?: string,
    operationData?: any,
    operator: string = 'system',
  ): Promise<void> {
    try {
      await this.prisma.appointment_operation_log.create({
        data: {
          appointment_id: appointmentId,
          operation_type: operationType,
          operation_data: operationData ? JSON.stringify(operationData) : undefined,
          result_status: resultStatus,
          result_message: resultMessage,
          operator,
        },
      })
    }
    catch (error) {
      this.logger.warn('记录操作日志失败:', error)
    }
  }

  /**
   * 创建预约记录
   *
   * @description 创建新的预约记录，包括自动生成序号、验证数据唯一性、处理并发冲突等。
   * 支持创建普通预约记录和占号数据。
   *
   * @param {CreateAppointmentInput} input - 预约记录创建输入数据
   * @param {object} [options] - 创建选项
   * @param {boolean} [options.asPlaceholder] - 是否作为占号数据创建，默认 false
   *
   * @returns {Promise<AppointmentManagement>} 创建成功的预约记录
   *
   * @throws {Error} 当护照号和驾照号组合已存在时抛出错误
   * @throws {Error} 当生成唯一序号失败时抛出错误（重试5次后）
   *
   * @example
   * ```typescript
   * // 创建普通预约记录
   * const appointment = await service.createAppointment({
   *   passport_number: 'P123456',
   *   license_number: 'L123456789',
   *   full_name: '张三',
   *   email: 'zhangsan@example.com',
   *   preferred_dates: ['25-12-2024', '26-12-2024'],
   *   priority: 1
   * })
   *
   * // 创建占号数据
   * const placeholder = await service.createAppointment(input, { asPlaceholder: true })
   * ```
   */
  async createAppointment(input: CreateAppointmentInput, options?: { asPlaceholder?: boolean }): Promise<AppointmentManagement> {
    try {
      // 生成序号
      const sequenceNumber = input.sequence_number || await this.generateSequenceNumber()

      // 检查是否已存在相同的护照号和驾照号
      const existing = await this.prisma.appointment_management.findUnique({
        where: {
          passport_number_license_number: {
            passport_number: input.passport_number,
            license_number: input.license_number,
          },
        },
      })

      if (existing) {
        throw new Error('该护照号和驾照号的记录已存在')
      }

      // 创建记录（处理并发唯一键冲突，最多重试5次）
      let appointment: any = null
      for (let attempt = 0; attempt < 5; attempt++) {
        const seq = attempt === 0 ? sequenceNumber : await this.generateSequenceNumber()
        try {
          const createData: any = {
            sequence_number: seq,
            passport_number: input.passport_number,
            license_number: input.license_number,
            full_name: input.full_name,
            email: input.email,
            phone_number: input.phone_number,
            issuing_country: input.issuing_country || 'CN',
            issuing_authority: input.issuing_authority || 'GD',
            service_type: input.service_type || 'DI',
            preferred_dates: (() => {
              const norm = this.normalizePreferredDates(input.preferred_dates)
              return norm ? JSON.stringify(norm) : undefined
            })(),
            preferred_times: input.preferred_times ? JSON.stringify(input.preferred_times) : undefined,
            priority: input.priority || 1,
            remarks: input.remarks,
            is_placeholder: options?.asPlaceholder === true,
            office_code: input.office_code || 'HKLO',
          }
          appointment = await this.prisma.appointment_management.create({
            data: createData,
          })
          // 兜底：若 Prisma 客户端类型未包含 user_agent 字段，则用原生 SQL 回填
          try {
            const ua = pickOrGenerateUA((input as any)?.user_agent)
            await (this.prisma as any).$executeRawUnsafe('UPDATE appointment_management SET user_agent=? WHERE id=?;', ua, appointment.id)
            appointment = { ...(appointment as any), user_agent: ua }
          }
          catch {}
          // 兜底：旧 Prisma 客户端不识别 force_preferred_dates，创建后用原生 SQL 回填
          try {
            if (typeof (input as any).force_preferred_dates === 'boolean') {
              const flag = (input as any).force_preferred_dates ? 1 : 0
              await (this.prisma as any).$executeRawUnsafe('UPDATE appointment_management SET force_preferred_dates=? WHERE id=?;', flag, appointment.id)
              appointment = { ...(appointment as any), force_preferred_dates: !!(input as any).force_preferred_dates }
            }
          }
          catch {}
          // 兼容：旧 Prisma 客户端不识别 is_monitor，创建后用原生 SQL 回填
          if (await this.hasIsMonitorColumn() && Boolean((input as any).is_monitor) === true) {
            try {
              await (this.prisma as any).$executeRawUnsafe('UPDATE appointment_management SET is_monitor=1 WHERE id=?;', appointment.id)
              appointment = { ...(appointment as any), is_monitor: true }
            }
            catch {}
          }
          break
        }
        catch (err: any) {
          if (err?.code === 'P2002' && err?.meta?.target?.includes('sequence_number')) {
            // 唯一键冲突，重试
            await new Promise(r => setTimeout(r, 10 + Math.floor(Math.random() * 40)))
            continue
          }
          throw err
        }
      }
      if (!appointment) {
        throw new Error('生成唯一序号失败，请稍后重试')
      }

      // 记录操作日志
      await this.logOperation(appointment.id, 'create', 'success', '创建预约记录', input)

      this.logger.info('✅ 创建预约记录成功', {
        id: (appointment as any).id,
        sequence_number: (appointment as any).sequence_number,
        is_placeholder: !!(appointment as any).is_placeholder,
        service_type: (appointment as any).service_type,
        office_code: (appointment as any).office_code,
      })
      return appointment as AppointmentManagement
    }
    catch (error: any) {
      this.logger.error('创建预约记录失败:', error)
      throw error
    }
  }

  /**
   * 获取预约记录
   *
   * @description 根据ID获取单个预约记录的完整信息
   *
   * @param {number} id - 预约记录ID
   * @returns {Promise<AppointmentManagement | null>} 预约记录对象，不存在时返回null
   *
   * @example
   * ```typescript
   * const appointment = await service.getAppointment(123)
   * if (appointment) {
   *   console.log(appointment.full_name)
   * }
   * ```
   */
  async getAppointment(id: number): Promise<AppointmentManagement | null> {
    try {
      const appointment = await this.prisma.appointment_management.findUnique({
        where: { id },
      })
      // 兼容：旧 Prisma 客户端可能未生成 user_agent 字段类型，使用原生 SQL 回填
      if (appointment && (appointment as any).user_agent === undefined) {
        try {
          const rows: any[] = await (this.prisma as any).$queryRawUnsafe(
            'SELECT user_agent FROM appointment_management WHERE id=?;',
            id,
          )
          const ua = Array.isArray(rows) && rows.length > 0 ? String(rows[0]?.user_agent || '') : ''
          ;(appointment as any).user_agent = ua || undefined
        }
        catch {}
      }
      return appointment as AppointmentManagement | null
    }
    catch (error: any) {
      this.logger.error('获取预约记录失败:', error)
      throw error
    }
  }

  /**
   * 通过序号获取预约记录
   *
   * @description 根据序号获取预约记录的完整信息
   *
   * @param {string} sequenceNumber - 预约记录序号
   * @returns {Promise<AppointmentManagement | null>} 预约记录对象，不存在时返回null
   *
   */
  async getAppointmentBySequence(sequenceNumber: string): Promise<AppointmentManagement | null> {
    try {
      const appointment = await this.prisma.appointment_management.findUnique({
        where: { sequence_number: sequenceNumber },
      })
      return appointment as AppointmentManagement | null
    }
    catch (error: any) {
      this.logger.error('通过序号获取预约记录失败:', error)
      throw error
    }
  }

  /**
   * 更新预约记录
   *
   * @description 更新指定ID的预约记录，支持部分字段更新。
   * 自动处理数组字段的JSON序列化和预约状态变更的时间记录。
   *
   * @param {number} id - 预约记录ID
   * @param {UpdateAppointmentInput} input - 更新数据
   * @returns {Promise<AppointmentManagement>} 更新后的预约记录
   *
   * @throws {Error} 当记录不存在或更新失败时抛出错误
   *
   * @example
   * ```typescript
   * const updatedAppointment = await service.updateAppointment(123, {
   *   full_name: '李四',
   *   priority: 2,
   *   preferred_dates: ['27-12-2024'],
   *   appointment_status: 'booked'
   * })
   * ```
   */
  async updateAppointment(id: number, input: UpdateAppointmentInput): Promise<AppointmentManagement> {
    try {
      // 读取当前记录以判断状态流转，避免误改 booked_at
      const existing = await this.prisma.appointment_management.findUnique({ where: { id } })
      if (!existing)
        throw new Error('预约记录不存在')

      const updateData: any = { ...input }
      // 提取前端展示用的下一次重试时间（不入库，仅广播给渲染层）
      const nextRetryAt: any = (input as any)?.next_retry_at
      if ('next_retry_at' in updateData)
        delete updateData.next_retry_at

      // Prisma 旧客户端兼容：先取出 is_monitor 意图，避免直接传入导致 Unknown argument
      const wantsMonitor: undefined | boolean = (typeof (input as any).is_monitor === 'boolean') ? (input as any).is_monitor : undefined
      if ('is_monitor' in updateData)
        delete updateData.is_monitor

      // 处理 created_at：使用原生 SQL 更新，因为 Prisma 可能不允许直接更新带默认值的字段
      const wantsCreatedAt: Date | undefined = input.created_at instanceof Date ? input.created_at : undefined
      if ('created_at' in updateData)
        delete updateData.created_at

      // 处理数组字段
      if (input.preferred_dates) {
        const norm = this.normalizePreferredDates(input.preferred_dates)
        if (norm)
          updateData.preferred_dates = JSON.stringify(norm)
        else
          updateData.preferred_dates = null
      }
      if (input.preferred_times) {
        updateData.preferred_times = JSON.stringify(input.preferred_times)
      }

      // UA 更新（可选）
      if (typeof (input as any)?.user_agent === 'string') {
        updateData.user_agent = String((input as any).user_agent)
      }

      // 参考编号处理：空字符串置为 null；非空唯一性预检
      if (typeof input.reference_number === 'string') {
        const trimmed = input.reference_number.trim()
        if (!trimmed) {
          updateData.reference_number = null
        }
        else if (trimmed !== (existing.reference_number || '')) {
          const dup = await this.prisma.appointment_management.findFirst({
            where: { reference_number: trimmed, NOT: { id } },
            select: { id: true },
          })
          if (dup) {
            throw new Error('参考编号已存在，请检查是否重复或选择其他编号')
          }
          updateData.reference_number = trimmed
        }
      }

      // 允许布尔字段 force_preferred_dates 更新
      if (typeof (input as any).force_preferred_dates === 'boolean') {
        updateData.force_preferred_dates = (input as any).force_preferred_dates
      }
      else if ('force_preferred_dates' in updateData) {
        delete updateData.force_preferred_dates
      }

      // 预约日期统一存储为 YYYY-MM-DD（兼容旧格式传入）
      if (typeof input.appointment_date === 'string') {
        const ymd = this.normalizeDateToYMD(input.appointment_date)
        if (ymd)
          updateData.appointment_date = ymd
      }

      // 预约时间统一格式为 HH:MM（移除秒部分）
      if (typeof input.appointment_time === 'string') {
        updateData.appointment_time = normalizeAppointmentTime(input.appointment_time)
      }

      // 仅在从非 booked -> booked 的转变，或首次设置参考号时，自动补齐 booked_at
      const refProvided = typeof input.reference_number === 'string' && input.reference_number.trim().length > 0
      const isRefNewlySet = refProvided && !existing.reference_number

      // 计算下一状态（未显式提供则沿用原状态）
      const nextStatus = typeof updateData.appointment_status === 'string'
        ? updateData.appointment_status
        : (isRefNewlySet ? 'booked' : existing.appointment_status)

      if (isRefNewlySet && nextStatus !== 'booked') {
        updateData.appointment_status = 'booked'
      }

      // 防护：若记录已成功（已是 booked 或已有 reference_number），不允许被并发失败结果降级为 failed/pending。
      // 典型场景：并发重试中先成功后失败，失败的写库请求晚到，覆盖为 failed，导致 UI 显示异常。
      if (
        (existing.appointment_status === 'booked' || !!existing.reference_number)
        && (updateData.appointment_status === 'failed' || updateData.appointment_status === 'pending')
      ) {
        delete updateData.appointment_status
      }

      const isTransitionToBooked = existing.appointment_status !== 'booked' && nextStatus === 'booked'
      const shouldAutofillBookedAt = !updateData.booked_at && !existing.booked_at && (isTransitionToBooked || isRefNewlySet)
      if (shouldAutofillBookedAt) {
        updateData.booked_at = dayjs().millisecond(0).toDate()
      }

      const appointment = await this.prisma.appointment_management.update({
        where: { id },
        data: updateData,
      })

      // 兼容：若要将 is_monitor 设为 true/false 且 Prisma 旧客户端不识别该字段，用原生 SQL 落库
      if (await this.hasIsMonitorColumn() && typeof wantsMonitor === 'boolean') {
        try {
          const flag = wantsMonitor ? 1 : 0
          await (this.prisma as any).$executeRawUnsafe('UPDATE appointment_management SET is_monitor=? WHERE id=?;', flag, id)
          ;(appointment as any).is_monitor = wantsMonitor
        }
        catch {}
      }

      // 更新 created_at：使用原生 SQL，因为 Prisma 可能不允许直接更新带默认值的字段
      if (wantsCreatedAt) {
        try {
          await (this.prisma as any).$executeRawUnsafe('UPDATE appointment_management SET created_at=? WHERE id=?;', wantsCreatedAt, id)
          ;(appointment as any).created_at = wantsCreatedAt
        }
        catch (e) {
          this.logger.warn(`更新 created_at 失败: ${e instanceof Error ? e.message : String(e)}`)
        }
      }

      // 记录操作日志
      await this.logOperation(id, 'update', 'success', '更新预约记录', input)

      const fromStatus = (existing as any).appointment_status
      const toStatus = (appointment as any).appointment_status
      const fromRef = (existing as any).reference_number || null
      const toRef = (appointment as any).reference_number || null
      const fromDate = (existing as any).appointment_date || null
      const toDate = (appointment as any).appointment_date || null
      const fromTime = (existing as any).appointment_time || null
      const toTime = (appointment as any).appointment_time || null
      const significant = (fromStatus !== toStatus) || (fromRef !== toRef) || (fromDate !== toDate) || (fromTime !== toTime)

      const payload = {
        id,
        sequence_number: (appointment as any).sequence_number,
        from_status: fromStatus,
        to_status: toStatus,
        reference_number: toRef,
        appointment_date: toDate,
        appointment_time: toTime,
      }
      if (significant)
        this.logger.info('✅ 更新预约记录成功', payload)
      else
        this.logger.debug('✅ 更新预约记录成功（无重要变更）', payload)

      // 广播预约更新事件到所有窗口，便于渲染层实时刷新状态
      try {
        const { BrowserWindow } = await import('electron')
        const payload: any = {
          id: appointment.id,
          sequence_number: (appointment as any).sequence_number,
          appointment_status: (appointment as any).appointment_status,
          reference_number: (appointment as any).reference_number,
          appointment_date: (appointment as any).appointment_date,
          appointment_time: (appointment as any).appointment_time,
          booked_at: (appointment as any).booked_at,
          updated_at: (appointment as any).updated_at,
        }
        if (nextRetryAt)
          payload.next_retry_at = nextRetryAt
        for (const win of BrowserWindow.getAllWindows()) {
          try { win.webContents.send('appointment:updated', payload) }
          catch {}
        }
      }
      catch {}
      return appointment as AppointmentManagement
    }
    catch (error: any) {
      this.logger.error('更新预约记录失败:', error)
      await this.logOperation(id, 'update', 'failed', error?.message, input)
      throw error
    }
  }

  /**
   * 删除预约记录
   *
   * @description 删除指定ID的预约记录
   *
   * @param {number} id - 预约记录ID
   * @returns {Promise<void>} 无返回值
   */
  async deleteAppointment(id: number): Promise<void> {
    try {
      const appointment = await this.getAppointment(id)
      if (!appointment) {
        throw new Error('预约记录不存在')
      }

      await this.prisma.appointment_management.delete({
        where: { id },
      })

      // 记录操作日志（使用已删除记录的ID）
      await this.logOperation(id, 'cancel', 'success', '删除预约记录')

      this.logger.info('✅ 删除预约记录成功', {
        id: (appointment as any).id,
        sequence_number: (appointment as any).sequence_number,
      })
    }
    catch (error: any) {
      this.logger.error('删除预约记录失败:', error)
      await this.logOperation(id, 'cancel', 'failed', error?.message)
      throw error
    }
  }

  /**
   * 查询预约记录列表
   *
   * @description 根据过滤条件和分页参数查询预约记录列表，支持多种筛选条件和排序。
   *
   * @param {AppointmentFilters} [filters] - 查询过滤条件，默认为空对象
   * @param {PaginationParams} [pagination] - 分页参数
   * @param {number} [pagination.page] - 页码（从1开始），默认为 1
   * @param {number} [pagination.page_size] - 每页记录数（最大1000），默认为 20
   *
   * @returns {Promise<AppointmentListResult>} 查询结果，包含记录列表和分页信息
   *
   * @example
   * ```typescript
   * // 查询待处理的预约记录
   * const result = await service.getAppointmentsList(
   *   { appointment_status: ['pending'] },
   *   { page: 1, page_size: 10 }
   * )
   * console.log(`共${result.total}条记录，当前页${result.appointments.length}条`)
   *
   * // 搜索指定用户的记录
   * const userRecords = await service.getAppointmentsList(
   *   { search: '张三' },
   *   { page: 1, page_size: 50, sort_field: 'created_at', sort_order: 'desc' }
   * )
   * ```
   */
  async getAppointmentsList(
    filters: AppointmentFilters = {},
    pagination: PaginationParams = { page: 1, page_size: 20 },
  ): Promise<AppointmentListResult> {
    try {
      const where: any = {}

      // 构建查询条件
      if (filters.appointment_status?.length) {
        where.appointment_status = { in: filters.appointment_status }
      }

      if (filters.is_placeholder !== undefined) {
        where.is_placeholder = filters.is_placeholder
      }

      // is_monitor 过滤：Prisma 旧客户端不识别该字段，改用原生SQL查询ID集合再拼接到 where.id in/notIn
      let monitorIdList: number[] | null = null
      const hasMonitorCol = await this.hasIsMonitorColumn()
      // 监控类型筛选：
      // - 监控(true)：where.id in (is_monitor=1)
      // - 非监控(false)：where.id not in (is_monitor=1)
      if (hasMonitorCol && typeof (filters as any).is_monitor === 'boolean') {
        try {
          const rows: any[] = await (this.prisma as any).$queryRawUnsafe(
            'SELECT id FROM appointment_management WHERE is_monitor = 1;',
          )
          monitorIdList = Array.isArray(rows) ? rows.map((r: any) => Number(r.id)).filter((n: any) => Number.isFinite(n)) : []
          if ((filters as any).is_monitor === true) {
            ;(where as any).id = { in: monitorIdList.length ? monitorIdList : [-1] }
          }
          else {
            ;(where as any).id = { notIn: monitorIdList }
          }
        }
        catch {
          // 查询失败则忽略该筛选，保证页面不报错
        }
      }

      if (filters.priority?.length) {
        where.priority = { in: filters.priority }
      }

      if (filters.issuing_country?.length) {
        where.issuing_country = { in: filters.issuing_country }
      }

      if (filters.service_type?.length) {
        where.service_type = { in: filters.service_type }
      }

      if (filters.office_code?.length) {
        where.office_code = { in: filters.office_code }
      }

      if (filters.batch_id) {
        where.batch_id = filters.batch_id
      }

      if (filters.date_range) {
        // 按创建时间过滤：仅在传入有效日期时设置条件，支持只传开始或只传结束
        const rawStart: any = (filters as any).date_range?.start
        const rawEnd: any = (filters as any).date_range?.end
        const cond: any = {}
        if (typeof rawStart === 'string' && rawStart.trim()) {
          const s = dayjs(rawStart)
          if (s.isValid())
            cond.gte = s.startOf('day').toDate()
        }
        if (typeof rawEnd === 'string' && rawEnd.trim()) {
          const e = dayjs(rawEnd)
          if (e.isValid())
            cond.lte = e.endOf('day').toDate()
        }
        if (Object.keys(cond).length > 0)
          where.created_at = cond
      }

      // 新增：按预约成功时间 booked_at 过滤（支持日期时间范围）
      if (filters.booked_at_range?.start && filters.booked_at_range?.end) {
        const startStr = String(filters.booked_at_range.start).trim()
        const endStr = String(filters.booked_at_range.end).trim()

        // 解析开始时间：明确指定格式以避免时区问题
        let start: dayjs.Dayjs
        if (/^\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2}$/.test(startStr)) {
          // 日期时间格式：YYYY-MM-DD HH:mm:ss，按本地时间解析
          start = dayjs(startStr, 'YYYY-MM-DD HH:mm:ss')
        }
        else if (/^\d{4}-\d{2}-\d{2}$/.test(startStr)) {
          // 只有日期格式：YYYY-MM-DD，设置为当天的开始时间
          start = dayjs(startStr, 'YYYY-MM-DD').startOf('day')
        }
        else {
          // 其他格式，使用默认解析
          start = dayjs(startStr)
        }

        // 解析结束时间
        let end: dayjs.Dayjs
        if (/^\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2}$/.test(endStr)) {
          // 日期时间格式：YYYY-MM-DD HH:mm:ss，按本地时间解析
          end = dayjs(endStr, 'YYYY-MM-DD HH:mm:ss')
        }
        else if (/^\d{4}-\d{2}-\d{2}$/.test(endStr)) {
          // 只有日期格式：YYYY-MM-DD，设置为当天的结束时间
          end = dayjs(endStr, 'YYYY-MM-DD').endOf('day')
        }
        else {
          // 其他格式，使用默认解析
          end = dayjs(endStr)
        }

        // 验证解析结果是否有效
        if (start.isValid() && end.isValid()) {
          // 确保开始时间 <= 结束时间
          if (!start.isAfter(end)) {
            const startDate = start.toDate()
            const endDate = end.toDate()
            where.booked_at = { gte: startDate, lte: endDate }
            this.logger.debug(`预约成功时间筛选: ${start.format('YYYY-MM-DD HH:mm:ss')} (${startDate.toISOString()}) 至 ${end.format('YYYY-MM-DD HH:mm:ss')} (${endDate.toISOString()})`)
          }
          else {
            this.logger.warn(`预约成功时间筛选范围无效: 开始时间 ${start.format('YYYY-MM-DD HH:mm:ss')} 晚于结束时间 ${end.format('YYYY-MM-DD HH:mm:ss')}`)
          }
        }
        else {
          this.logger.warn(`预约成功时间筛选参数无效: start=${startStr} (valid=${start.isValid()}), end=${endStr} (valid=${end.isValid()})`)
        }
      }

      // 新增：按预约日期范围过滤。数据库中 appointment_date 统一为 YYYY-MM-DD（兼容历史数据中的 DD-MM-YYYY）。
      // 处理策略：将范围展开为日期列表，对每个日期同时匹配 YYYY-MM-DD 与 DD-MM-YYYY 两种 equals 条件。
      if (filters.appointment_date_range?.start && filters.appointment_date_range?.end) {
        const start = String(filters.appointment_date_range.start)
        const end = String(filters.appointment_date_range.end)

        const normalizeYMD = (v: string) => {
          if (/^\d{2}-\d{2}-\d{4}$/.test(v)) {
            const [dd, mm, yyyy] = v.split('-')
            return `${yyyy}-${mm}-${dd}`
          }
          return v
        }

        const startY = dayjs(normalizeYMD(start))
        const endY = dayjs(normalizeYMD(end))
        if (startY.isValid() && endY.isValid() && !endY.isBefore(startY)) {
          const or: any[] = []
          let cur = startY.startOf('day')
          const endDay = endY.startOf('day')
          while (cur.isBefore(endDay) || cur.isSame(endDay)) {
            const ymd = cur.format('YYYY-MM-DD')
            const [yyyy, mm, dd] = [cur.year(), cur.month() + 1, cur.date()].map((x, i) => i === 0 ? String(x) : String(x).padStart(2, '0')) as any
            const dmy = `${String(dd).padStart(2, '0')}-${String(mm).padStart(2, '0')}-${String(yyyy)}`
            or.push({ appointment_date: { equals: ymd } })
            or.push({ appointment_date: { equals: dmy } })
            cur = cur.add(1, 'day')
          }
          if (or.length > 0)
            where.OR = (where.OR || []).concat(or)
        }
      }

      if (filters.search) {
        const trimmed = String(filters.search).trim()
        if (trimmed.length > 0) {
          const tokens = trimmed.split(/\s+/g)

          // 多关键词 AND、字段 OR 的模糊匹配
          where.AND = tokens.map(token => ({
            OR: [
              { full_name: { contains: token } },
              { passport_number: { contains: token } },
              { license_number: { contains: token } },
              { email: { contains: token } },
              { phone_number: { contains: token } },
              { sequence_number: { contains: token } },
              { reference_number: { contains: token } },
              { remarks: { contains: token } },
            ],
          }))
        }
      }

      // 确保分页参数有效
      const safePagination = {
        page: Math.max(1, pagination?.page || 1),
        page_size: Math.max(1, Math.min(1000, pagination?.page_size || 20)),
        sort_field: pagination?.sort_field,
        sort_order: pagination?.sort_order,
      }

      // 计算分页
      const skip = (safePagination.page - 1) * safePagination.page_size

      // 排序
      const orderBy: any = {}
      if (safePagination.sort_field) {
        orderBy[safePagination.sort_field] = safePagination.sort_order || 'desc'
      }
      else {
        orderBy.created_at = 'desc'
      }

      // 查询数据
      const [appointments, total] = await Promise.all([
        this.prisma.appointment_management.findMany({
          where,
          skip,
          take: safePagination.page_size,
          orderBy,
        }),
        this.prisma.appointment_management.count({ where }),
      ])

      // 兼容：旧 Prisma 客户端缺少 is_monitor 类型，使用原生 SQL 回填
      let enriched = appointments as any[]
      if (await this.hasIsMonitorColumn()) {
        const ids = Array.isArray(appointments) ? appointments.map((a: any) => a.id).filter((n: any) => Number.isFinite(n)) : []
        if (ids.length > 0) {
          try {
            const placeholders = ids.map(() => '?').join(',')
            const rows: any[] = await (this.prisma as any).$queryRawUnsafe(
              `SELECT id, is_monitor FROM appointment_management WHERE id IN (${placeholders});`,
              ...ids,
            )
            const idToMonitor: Record<number, boolean> = {}
            for (const r of (rows || []))
              idToMonitor[Number(r.id)] = Number(r.is_monitor) === 1
            enriched = appointments.map((a: any) => ({ ...a, is_monitor: !!idToMonitor[a.id] }))
          }
          catch {}
        }
      }

      // 兼容：旧 Prisma 客户端未包含 user_agent 字段类型，使用原生 SQL 回填
      try {
        const ids = Array.isArray(enriched) ? enriched.map((a: any) => a.id).filter((n: any) => Number.isFinite(n)) : []
        if (ids.length > 0) {
          const placeholders = ids.map(() => '?').join(',')
          const rows: any[] = await (this.prisma as any).$queryRawUnsafe(
            `SELECT id, user_agent FROM appointment_management WHERE id IN (${placeholders});`,
            ...ids,
          )
          const idToUa: Record<number, string | undefined> = {}
          for (const r of (rows || []))
            idToUa[Number(r.id)] = (r?.user_agent ? String(r.user_agent) : undefined)
          enriched = enriched.map((a: any) => ({ ...a, user_agent: (a as any).user_agent ?? idToUa[a.id] }))
        }
      }
      catch {}

      return {
        appointments: enriched as AppointmentManagement[],
        total,
        page: pagination.page,
        page_size: pagination.page_size,
        total_pages: Math.ceil(total / pagination.page_size),
      }
    }
    catch (error: any) {
      this.logger.error('查询预约记录失败:', error)
      throw error
    }
  }

  /**
   * 批量操作
   *
   * @description 对多个预约记录执行批量操作，支持预约、取消、删除、释放、更新优先级等操作。
   * 操作结果包含成功和失败的统计信息。
   *
   * @param {BatchOperationRequest} request - 批量操作请求
   * @param {number[]} request.appointment_ids - 要操作的预约记录ID数组
   * @param {string} request.operation - 操作类型：book/cancel/delete/release/update_priority/delete_all
   * @param {any} [request.data] - 操作相关数据（如优先级更新的新值）
   *
   * @returns {Promise<BatchOperationResult>} 批量操作结果统计
   *
   * @example
   * ```typescript
   * // 批量取消预约
   * const result = await service.batchOperation({
   *   appointment_ids: [1, 2, 3],
   *   operation: 'cancel'
   * })
   * console.log(`成功: ${result.success_count}, 失败: ${result.failed_count}`)
   *
   * // 批量更新优先级
   * const priorityResult = await service.batchOperation({
   *   appointment_ids: [4, 5, 6],
   *   operation: 'update_priority',
   *   data: { priority: 1 }
   * })
   *
   * // 清空所有数据
   * const clearResult = await service.batchOperation({
   *   appointment_ids: [],
   *   operation: 'delete_all'
   * })
   * ```
   */
  async batchOperation(request: BatchOperationRequest): Promise<BatchOperationResult> {
    const result: BatchOperationResult = {
      success: true,
      total_count: request.appointment_ids.length,
      success_count: 0,
      failed_count: 0,
      errors: [],
    }

    // 清空所有数据
    if (request.operation === 'delete_all') {
      try {
        const del = await this.prisma.appointment_management.deleteMany({})
        result.success_count = del.count
        result.total_count = del.count
        result.failed_count = 0
      }
      catch (error: any) {
        result.success = false
        result.errors.push({ appointment_id: -1, message: error.message })
      }
      return result
    }

    for (const appointmentId of request.appointment_ids) {
      try {
        switch (request.operation) {
          case 'book':
            await this.bookAppointment(appointmentId, request.data)
            break
          case 'cancel':
            await this.updateAppointment(appointmentId, { appointment_status: 'cancelled' })
            // 明确记录一次取消操作日志，便于与普通更新区分
            await this.logOperation(appointmentId, 'cancel', 'success', '远程取消：状态标记为已取消')
            break
          case 'delete':
            await this.deleteAppointment(appointmentId)
            break
          case 'release':
            await this.releaseAppointment(appointmentId)
            break
          case 'update_priority':
            await this.updateAppointment(appointmentId, { priority: request.data.priority })
            break
          default:
            throw new Error(`不支持的操作: ${request.operation}`)
        }
        result.success_count++
      }
      catch (error: any) {
        result.failed_count++
        result.errors.push({
          appointment_id: appointmentId,
          message: error?.message,
        })
        // 失败也记录取消日志，便于溯源
        if (request.operation === 'cancel') {
          try { await this.logOperation(appointmentId, 'cancel', 'failed', error?.message) }
          catch {}
        }
      }
    }

    if (result.failed_count > 0) {
      result.success = false
    }

    return result
  }

  /**
   * 预约操作
   *
   * @description 预约指定ID的预约记录
   *
   * @param {number} id - 预约记录ID
   * @param {any} bookingData - 预约数据
   * @returns {Promise<AppointmentManagement>} 预约记录对象
   */
  async bookAppointment(id: number, bookingData: any): Promise<AppointmentManagement> {
    try {
      const appointment = await this.updateAppointment(id, {
        appointment_status: 'booked',
        appointment_date: bookingData.appointment_date,
        appointment_time: normalizeAppointmentTime(bookingData.appointment_time),
        timeslot_id: bookingData.timeslot_id,
        reference_number: bookingData.reference_number,
      })

      await this.logOperation(id, 'book', 'success', '预约成功', bookingData)
      return appointment
    }
    catch (error: any) {
      await this.logOperation(id, 'book', 'failed', error.message, bookingData)
      throw error
    }
  }

  /**
   * 释放占号
   *
   * @description 释放指定ID的占号记录
   *
   * @param {number} id - 预约记录ID
   * @returns {Promise<AppointmentManagement>} 预约记录对象
   */
  async releaseAppointment(id: number): Promise<AppointmentManagement> {
    try {
      const appointment = await this.getAppointment(id)
      if (!appointment?.is_placeholder) {
        throw new Error('只能释放占号数据')
      }

      const updated = await this.updateAppointment(id, {
        appointment_status: 'cancelled',
        error_message: '占号已释放',
      })

      await this.logOperation(id, 'release', 'success', '释放占号')
      return updated
    }
    catch (error: any) {
      await this.logOperation(id, 'release', 'failed', error.message)
      throw error
    }
  }

  /**
   * 获取操作日志
   *
   * @description 获取指定ID的预约记录的操作日志
   *
   * @param {number} appointmentId - 预约记录ID
   * @returns {Promise<any[]>} 操作日志数组
   */
  async getOperationLogs(appointmentId: number): Promise<any[]> {
    try {
      const logs = await this.prisma.appointment_operation_log.findMany({
        where: { appointment_id: appointmentId },
        orderBy: { timestamp: 'desc' },
      })
      return logs
    }
    catch (error: any) {
      this.logger.error('获取操作日志失败:', error)
      throw error
    }
  }

  /**
   * 获取统计信息
   *
   * @description 获取预约系统的各项统计数据，包括总数、各状态数量、今日新增等。
   *
   * @returns {Promise<any>} 统计信息对象
   * @returns {number} returns.total_count - 总预约记录数
   * @returns {number} returns.pending_count - 待处理记录数
   * @returns {number} returns.booked_count - 已预约记录数
   * @returns {number} returns.placeholder_count - 占号数据数
   * @returns {number} returns.today_count - 今日新增记录数
   * @returns {number} returns.real_data_count - 真实数据记录数（排除占号）
   *
   * @example
   * ```typescript
   * const stats = await service.getStatistics()
   * console.log(`总计: ${stats.total_count}`)
   * console.log(`待处理: ${stats.pending_count}`)
   * console.log(`已预约: ${stats.booked_count}`)
   * console.log(`今日新增: ${stats.today_count}`)
   * ```
   */
  async getStatistics(): Promise<any> {
    try {
      const [totalCount, pendingCount, bookedCount, placeholderCount, todayCount] = await Promise.all([
        this.prisma.appointment_management.count(),
        this.prisma.appointment_management.count({ where: { appointment_status: 'pending' } }),
        this.prisma.appointment_management.count({ where: { appointment_status: 'booked' } }),
        this.prisma.appointment_management.count({ where: { is_placeholder: true } }),
        this.prisma.appointment_management.count({
          where: {
            created_at: {
              gte: dayjs().startOf('day').toDate(),
            },
          },
        }),
      ])

      // 使用原生 SQL 统计监控数据数量（兼容旧 Prisma 客户端）
      let monitorCount = 0
      if (await this.hasIsMonitorColumn()) {
        try {
          const rows: any[] = await (this.prisma as any).$queryRawUnsafe(`SELECT COUNT(1) AS cnt FROM appointment_management WHERE is_monitor=1;`)
          monitorCount = Number(rows?.[0]?.cnt || 0)
        }
        catch {}
      }

      return {
        total_count: totalCount,
        pending_count: pendingCount,
        booked_count: bookedCount,
        placeholder_count: placeholderCount,
        monitor_count: monitorCount,
        today_count: todayCount,
        real_data_count: totalCount - placeholderCount,
        // 预留：未来可以在此返回 release_record 统计（如当天记录数）
      }
    }
    catch (error: any) {
      this.logger.error('获取统计信息失败:', error)
      throw error
    }
  }

  /**
   * 清理过期占号数据
   *
   * @description 清理过期占号数据
   *
   * @param {number} hoursOld - 过期时间（小时）
   * @returns {Promise<number>} 清理的记录数
   */
  async cleanupExpiredPlaceholders(hoursOld: number = 24): Promise<number> {
    try {
      const cutoffTime = dayjs().subtract(hoursOld, 'hour').toDate()

      const result = await this.prisma.appointment_management.deleteMany({
        where: {
          is_placeholder: true,
          appointment_status: 'pending',
          created_at: {
            lt: cutoffTime,
          },
        },
      })

      this.logger.info(`🧹 清理了 ${result.count} 个过期占号数据`)
      return result.count
    }
    catch (error: any) {
      this.logger.error('清理过期占号数据失败:', error)
      throw error
    }
  }
}

// ============================================================================
// 全局服务实例管理
// ============================================================================

/** 全局服务实例 */
let appointmentService: AppointmentService | null = null

/**
 * 获取预约管理服务实例
 *
 * @description 获取全局唯一的预约管理服务实例，采用单例模式确保服务状态一致性。
 * 首次调用时会自动创建实例。
 *
 * @returns {AppointmentService} 预约管理服务实例
 *
 * @example
 * ```typescript
 * // 在任何地方获取服务实例
 * const appointmentService = getAppointmentService()
 *
 * // 创建预约记录
 * const appointment = await appointmentService.createAppointment({
 *   passport_number: 'P123456',
 *   license_number: 'L123456789',
 *   full_name: '张三',
 *   email: 'zhangsan@example.com'
 * })
 * ```
 */
export function getAppointmentService(): AppointmentService {
  if (!appointmentService) {
    appointmentService = new AppointmentService()
  }
  return appointmentService
}
